﻿/*
作为ManagerSql类的子类，对应采取sqlserver数据库特定的数据操作而扩展的类

 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBA.Data.ADO;
using DBA.Data.Hibernate;
using DBA.Common;

namespace DBA.Data
{
    class SqlClientManageSql : ManagerSQL
    {
        #region 构造函数
        public SqlClientManageSql(DataAccessLibrary dal):base(dal)
        {
            
        }
        #endregion

        /// <summary>
        /// 通过DATATABLE和表名批量插入到数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int Insert(DataTable dt, string tableName)
        {
            SqlBulkCopy bcp = null;
            int reValue = 0;
            
            if (dal.IsUseTrans) //如果开启了事务
            {
                bcp = new SqlBulkCopy((SqlConnection)dal.Command.Connection, SqlBulkCopyOptions.Default, (SqlTransaction)dal.Command.Transaction);
            }
            else
            {
                bcp = new SqlBulkCopy(dal.ConnectionString);
            }
            try
            {
                bcp.BatchSize = 1000;
                bcp.BulkCopyTimeout = 120;
                bcp.DestinationTableName = tableName;
                foreach (DataColumn columns in dt.Columns)
                {
                    bcp.ColumnMappings.Add(columns.ColumnName, columns.ColumnName);
                }
                bcp.WriteToServer(dt);
                reValue = 1;
            }
            catch
            {
                bcp.Close();
                throw new Exception();
            }
            finally
            {
                bcp.Close();
            }
            return reValue;
        }

        #region 私有方法
        /// <summary>
        /// 用来获得分页的记录统计
        /// </summary>
        /// <param name="tablename">多表方式传入的sql语句，单表方式传入查询的表名</param>
        /// <param name="tableType">表的类别</param>
        /// <param name="where">where条件</param>
        /// <param name="sb">sql字符串容器</param>
        /// <param name="recordCount">记录数</param>
        /// <returns>返回表得别名</returns>
        private string GetRecordCount(string tablename, PageViewTableType tableType, string where, StringBuilder sb, ref int recordCount)
        {
            string tableAlien = string.Empty;
            if (recordCount == 0)
            {
                if (string.IsNullOrEmpty(where))
                {
                    //如果是前两类的话,那么从SYSINDEXES表中直接取得所要查询的主表的数据量
                    switch (tableType)
                    {
                        case PageViewTableType.SimpleTable:
                            sb.Append("SELECT ROWS FROM SYSINDEXES WHERE OBJECT_ID(N'");
                            sb.Append(tablename);
                            sb.Append("') = ID AND indid < 2");
                            break;
                        case PageViewTableType.MixtureTable:
                            sb.Append("SELECT pageCount=COUNT(*) FROM ");
                            sb.Append(tablename);
                            sb.Append(" TMP ");
                            sb.Append(where);
                            tableAlien = " TMP ";
                            break;
                    }
                }
                else
                {
                    //如果是前两类的话,那么从SYSINDEXES表中直接取得所要查询的主表的数据量
                    switch (tableType)
                    {
                        case PageViewTableType.SimpleTable:
                            sb.Append("SELECT pageCount=COUNT(*) FROM ");
                            sb.Append(tablename);
                            sb.Append("  ");
                            sb.Append(where);
                            break;
                        case PageViewTableType.MixtureTable:
                            sb.Append("SELECT pageCount=COUNT(*) FROM ");
                            sb.Append(tablename);
                            sb.Append(" TMP ");
                            sb.Append(where);
                            tableAlien = " TMP ";
                            break;
                    }
                }
                try
                {
                    recordCount = Convert.ToInt32(this.dal.ExecuteString(sb.ToString()));
                }
                catch (Exception ex)
                {
                    throw new ArgumentNullException("Count is Wrong");
                }
            }
            else
            {
                if (tableType == PageViewTableType.MixtureTable)
                {
                    tableAlien = " TMP ";
                }
            }
            return tableAlien;
        }

        private string getPageSQL2005Str(string tablename, string fieldShow, string where, string sort, int pageCurrent, int pageSize,
                                                       PageViewTableType tableType, ref int recordCount)
        {
            if (string.IsNullOrEmpty(tablename))
            {
                throw new ArgumentNullException("Table Name Is Null");

            }

            if (string.IsNullOrEmpty(sort))
            {
                throw new ArgumentNullException("Index Sort Is Null");
            }


            if (tableType != PageViewTableType.MixtureTable && tableType != PageViewTableType.SimpleTable)
            {
                throw new ArgumentNullException("TableType Is Wrong");
            }

            sort = sort.ToUpper();
            //对参数进行判断和初始化
            fieldShow = string.IsNullOrEmpty(fieldShow) ? "*" : fieldShow;
            where = string.IsNullOrEmpty(where) ? where : "WHERE (" + where + ")";
            //表别名
            string tableAlien = string.Empty;
            pageCurrent = pageCurrent < 1 ? 1 : pageCurrent;
            pageSize = pageSize < 1 ? 20 : pageSize;
            string newSort = sort;
            StringBuilder sb = new StringBuilder();

            //统计记录数
            tableAlien = GetRecordCount(tablename, tableType, where, sb, ref recordCount);

            sb = new StringBuilder();

            //得到页码
            int pageCount = (recordCount + pageSize - 1) / pageSize;
            //得到页中值
            int pageHalf = (pageCount + 1) / 2;
            //如果当前页为中值的时候,那么将页码反转,同时,将以前排序为正的转为反,反的转为正
            if (pageCurrent > pageHalf)
            {
                //如果为偶数的对称数列的话,那么(recordCount+1)%2为1就进行了补充
                //pageCurrent = pageHalf - (pageCurrent - pageHalf - (pageCount + 1) % 2);
                string[] sortArray = (sort + ",").Split(',');
                sort = "ORDER BY ";
                for (int i = 0; i < sortArray.Length - 1; i++)
                {
                    if (sortArray[i].IndexOf("ASC") > -1)
                    {
                        sort += sortArray[i].Replace("ASC", "DESC") + ",";
                    }
                    else if (sortArray[i].IndexOf("DESC") > -1)
                    {
                        sort += sortArray[i].Replace("DESC", "ASC") + ",";
                    }
                    else
                    {
                        sort += sortArray[i] + " DESC" + ",";
                    }
                }
                sort = sort.Substring(0, sort.Length - 1);
                string topOne = Convert.ToString((recordCount - pageHalf * pageSize) - pageSize * (pageCurrent - pageHalf - 1));
                string topTwo = Convert.ToString(((recordCount - pageHalf * pageSize) - pageSize * (pageCurrent - pageHalf)));

                //如果是尾页,那么可以直接TOP
                if (pageCurrent == pageCount)
                {
                    sb.Append("SELECT * FROM (SELECT TOP ").Append(Convert.ToString(recordCount - (pageCurrent - 1) * pageSize)).Append(" ").
                       Append(fieldShow).Append(" FROM ").Append(tablename).Append(" ").Append(tableAlien).Append(" ").
                       Append(where).Append(" ").Append(sort).Append(") A ORDER BY ").Append(newSort);
                }
                else
                {
                    sb.Append("SELECT TOP ").Append(pageSize.ToString()).Append(" * FROM ( ").Append(" SELECT TOP ").
                        Append(topOne).Append(" ROW_NUMBER() OVER (").Append(sort).Append(") AS Maga_ROW, ").Append(fieldShow).
                        Append(" from ").Append(tablename).Append(" ").Append(tableAlien).Append(" ").Append(where).
                        Append(" ORDER BY Maga_ROW ASC ) A").Append(" WHERE A.Maga_ROW > (").Append(topTwo).Append(") ").Append(" ").
                        Append("ORDER BY Maga_ROW DESC ");
                }
            }
            else
            {
                sort = "ORDER BY " + sort;
                string topOne = Convert.ToString(pageCurrent * pageSize);
                string topTwo = Convert.ToString((pageCurrent - 1) * pageSize);

                //如果是首页,可以直接TOP
                if (pageCurrent == 1)
                {
                    sb.Append("SELECT * FROM (SELECT TOP ").Append(pageSize.ToString()).Append(" ").Append(fieldShow).
                        Append(" FROM ").Append(tablename).Append(" ").Append(tableAlien).Append(" ").Append(where).
                        Append(" ").Append("ORDER BY ").Append(newSort).Append(" ) A ");
                }
                else
                {
                    sb.Append("SELECT TOP ").Append(pageSize.ToString()).Append(" * FROM ( ").Append(" SELECT TOP ").Append(topOne).
                        Append(" ROW_NUMBER() OVER (").Append(sort).Append(") AS Maga_ROW, ").Append(fieldShow).Append(" from ").
                        Append(tablename).Append(" ").Append(tableAlien).Append(" ").Append(where).Append(" ORDER BY Maga_ROW ASC ) A").
                        Append(" WHERE A.Maga_ROW > (").Append(topTwo).Append(") ");
                }
            }

            return sb.ToString();
        }
        #endregion

        #region IDataPagination 成员
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="adoCondition"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public override IList<T> Select<T>(IList<AdoCondition> adoCondition, int pageCurrent, int pageSize, ref int recordCount) 
        {
            dal.ManagerParameter.ClearParameter();
            string where = string.Empty;
            string order = string.Empty;
            SqlParasContains sqlAndParas = null;
            OrmPropertyAttribute attr = AttributeHelper.GetAAttribute<OrmPropertyAttribute, T>();
            if (adoCondition!=null)
            {
                sqlAndParas = new SqlBuilder(adoCondition).GetSqlAndParas();
                
                if (!VerificationHelper.IsNullOrEmpty<string>(sqlAndParas.OrderStr))
                {
                    order = sqlAndParas.OrderStr;
                }
                if (!VerificationHelper.IsNullOrEmpty<string>(sqlAndParas.whereStr))
                {
                    where = "where " + sqlAndParas.whereStr.TrimStart(" and".ToCharArray()).TrimStart(" or".ToCharArray());
                }

                foreach (KeyValuePair<string, object> pair in sqlAndParas.paras)
                {
                    dal.ManagerParameter.AddNewInParameter(pair.Key, pair.Value);
                }
            }

            string sql = "select * from [{0}] {1}".FormatWith(attr.TableName, where);

            if (VerificationHelper.IsNullOrEmpty<string>(order))
            {
                order = " " + attr.PrimaryKey + " desc";
            }


            sql = this.getPageSQL2005Str("(" + sql + ")", "*", String.Empty, order, pageCurrent, pageSize, PageViewTableType.MixtureTable, ref recordCount);

            return dal.ExecuteFillObjects<T>(sql);
        }

        /// <summary>
        /// Author:samfeng
        /// SQLSERVER 2005分页，可以接left, right, inner等多种连接方法
        /// 主要原理是利用2005的ROW_NUMBER函数特性，然后根据选定的排序列进行排序(返回一个SqlDataReader),采用2分原理
        /// </summary>
        /// <param name="tablename">表名，可以是多表</param>
        /// <param name="fieldShow">字段显示</param>
        /// <param name="where">条件</param>
        /// <param name="sort">索引排序,这里是使用2005的新的函数ROW_NUMBER来根据排序来生成一个索引列</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="tableType">表类型</param>
        /// <param name="recordCount">记录总数</param>
        /// <returns>返回DataTable类型</returns>
        public override DataTable GetPageDataTable(string tablename, string fieldShow, string where, string sort, int pageCurrent, int pageSize, PageViewTableType tableType, ref int recordCount)
        {
            string sql = this.getPageSQL2005Str(tablename, fieldShow, where, sort, pageCurrent, pageSize, tableType, ref recordCount);

            return this.dal.ExecuteFillDataTable(sql);
        }
        #endregion
    }
}
